﻿Public Class ClsTVoucherDetailDbAccess
    Inherits ClsBaseDbAccess

    Public Sub New(ByVal s As String)
        MyBase.New(s, TABLE_NAME_T_VOUCHER_DETAIL)
    End Sub

    Public Sub New(ByVal s As String, ByVal n As String)
        MyBase.New(s, n)
    End Sub

    ''' <summary>
    ''' 根据指定科目编码修改分录中科目信息
    ''' </summary>
    ''' <param name="tmpCls"></param>
    ''' <param name="oldAcctCode"></param>
    ''' <returns></returns>
    Public Function UpdateAcctInEntry(ByVal tmpCls As ClsTAccount, ByVal oldAcctCode As String) As Integer
        Try

            Dim tmpSql As String
            Dim tmpDbAccess = GetConnection()

            Dim tmpParas(2) As OleDb.OleDbParameter
            Dim tmpReturn As Integer

            '打开数据库
            tmpDbAccess.DbOpen()

            '构造查询语句
            tmpSql = "update [" & Me.AppTableName & "] "
            tmpSql &= " set [AccountCode]=@AccountCode, [AccountCodeName]=@AccountCodeName "
            tmpSql &= " where [AccountCode]=@OldCode "

            '构造参数集合
            tmpParas(0) = New OleDb.OleDbParameter("@AccountCode", OleDb.OleDbType.VarWChar)
            tmpParas(1) = New OleDb.OleDbParameter("@AccountCodeName", OleDb.OleDbType.VarWChar)
            tmpParas(2) = New OleDb.OleDbParameter("@OldCode", OleDb.OleDbType.VarWChar)
            '为查询参数赋值
            tmpParas(0).Value = tmpCls.AccountCode
            tmpParas(1).Value = tmpCls.FullName
            tmpParas(2).Value = oldAcctCode

            '执行更新查询(插入、修改、删除)
            tmpReturn = tmpDbAccess.UpdateRecord(tmpSql, tmpParas)

            '关闭数据库
            tmpDbAccess.DbClose()

            Return tmpReturn

        Catch ex As OleDb.OleDbException

            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "ClsTVoucherDetailDbAccess - InsertEntry"

            Return 0
        End Try
    End Function

    ''' <summary>
    ''' 插入一条新的凭证明细记录
    ''' </summary>
    ''' <param name="tmpCls"></param>
    ''' <returns>非0值成功</returns>
    Public Function InsertEntry(ByVal tmpCls As ClsTVoucherDetail) As Integer
        Try

            Dim tmpSql As String
            Dim tmpDbAccess = GetConnection()

            Dim tmpParas(24) As OleDb.OleDbParameter
            Dim tmpReturn As Integer

            '打开数据库
            tmpDbAccess.DbOpen()

            '构造查询语句
            tmpSql = "insert into [" & Me.AppTableName & "] ("
            tmpSql &= "[VoucherId],[EntryNumber],[Excerpta],[AccountCode],"
            tmpSql &= "[AccountCodeName],[Debit],[Credit],[Quantity],[Price],[Unit],"
            tmpSql &= "[SettleMode],[SettleCode],[SettleDate],[Payer],[CurrencyCode],"
            tmpSql &= "[ExchangeRate],[CurrencyNumber],[TransCode],[ProjectName],"
            tmpSql &= "[ProjectCode],[ProjectCustomer],[VoucherNumber],[VoucherGroupId],[VoucherGroup],[WorkPeriod] "
            tmpSql &= ") values("
            tmpSql &= "@VoucherId,@EntryNumber,@Excerpta,@AccountCode,"
            tmpSql &= "@AccountCodeName,@Debit,@Credit,@Quantity,@Price,@Unit,"
            tmpSql &= "@SettleMode,@SettleCode,@SettleDate,@Payer,@CurrencyCode,"
            tmpSql &= "@ExchangeRate,@CurrencyNumber,@TransCode,@ProjectName,"
            tmpSql &= "@ProjectCode,@ProjectCustomer,@VoucherNumber,@VoucherGroupId,@VoucherGroup,@WorkPeriod "
            tmpSql &= ")"

            '构造参数集合
            tmpParas(0) = New OleDb.OleDbParameter("@VoucherId", OleDb.OleDbType.Integer)
            tmpParas(1) = New OleDb.OleDbParameter("@EntryNumber", OleDb.OleDbType.Integer)
            tmpParas(2) = New OleDb.OleDbParameter("@Excerpta", OleDb.OleDbType.VarWChar)
            tmpParas(3) = New OleDb.OleDbParameter("@AccountCode", OleDb.OleDbType.VarWChar)
            tmpParas(4) = New OleDb.OleDbParameter("@AccountCodeName", OleDb.OleDbType.VarWChar)
            tmpParas(5) = New OleDb.OleDbParameter("@Debit", OleDb.OleDbType.Decimal)
            tmpParas(6) = New OleDb.OleDbParameter("@Credit", OleDb.OleDbType.Decimal)
            tmpParas(7) = New OleDb.OleDbParameter("@Quantity", OleDb.OleDbType.Single)
            tmpParas(8) = New OleDb.OleDbParameter("@Price", OleDb.OleDbType.Decimal)
            tmpParas(9) = New OleDb.OleDbParameter("@Unit", OleDb.OleDbType.VarWChar)
            tmpParas(10) = New OleDb.OleDbParameter("@SettleMode", OleDb.OleDbType.VarWChar)
            tmpParas(11) = New OleDb.OleDbParameter("@SettleCode", OleDb.OleDbType.VarWChar)
            tmpParas(12) = New OleDb.OleDbParameter("@SettleDate", OleDb.OleDbType.Date)
            tmpParas(13) = New OleDb.OleDbParameter("@Payer", OleDb.OleDbType.VarWChar)
            tmpParas(14) = New OleDb.OleDbParameter("@CurrencyCode", OleDb.OleDbType.VarWChar)
            tmpParas(15) = New OleDb.OleDbParameter("@ExchangeRate", OleDb.OleDbType.Single)
            tmpParas(16) = New OleDb.OleDbParameter("@CurrencyNumber", OleDb.OleDbType.Single)
            tmpParas(17) = New OleDb.OleDbParameter("@TransCode", OleDb.OleDbType.VarWChar)
            tmpParas(18) = New OleDb.OleDbParameter("@ProjectName", OleDb.OleDbType.VarWChar)
            tmpParas(19) = New OleDb.OleDbParameter("@ProjectCode", OleDb.OleDbType.VarWChar)
            tmpParas(20) = New OleDb.OleDbParameter("@ProjectCustomer", OleDb.OleDbType.VarWChar)
            tmpParas(21) = New OleDb.OleDbParameter("@VoucherNumber", OleDb.OleDbType.Integer)
            tmpParas(22) = New OleDb.OleDbParameter("@VoucherGroupId", OleDb.OleDbType.Integer)
            tmpParas(23) = New OleDb.OleDbParameter("@VoucherGroup", OleDb.OleDbType.VarWChar)
            tmpParas(24) = New OleDb.OleDbParameter("@WorkPeriod", OleDb.OleDbType.VarWChar)

            '为查询参数赋值
            tmpParas(0).Value = tmpCls.VoucherId
            tmpParas(1).Value = tmpCls.EntryNumber
            tmpParas(2).Value = tmpCls.Excerpta
            tmpParas(3).Value = tmpCls.AccountCode
            tmpParas(4).Value = tmpCls.AccountCodeName
            tmpParas(5).Value = tmpCls.Debit
            tmpParas(6).Value = tmpCls.Credit
            tmpParas(7).Value = tmpCls.Quantity
            tmpParas(8).Value = tmpCls.Price
            tmpParas(9).Value = tmpCls.Unit
            tmpParas(10).Value = tmpCls.SettleMode
            tmpParas(11).Value = tmpCls.SettleCode
            tmpParas(12).Value = tmpCls.SettleDate
            tmpParas(13).Value = tmpCls.Payer
            tmpParas(14).Value = tmpCls.CurrencyCode
            tmpParas(15).Value = tmpCls.ExchangeRate
            tmpParas(16).Value = tmpCls.CurrencyNumber
            tmpParas(17).Value = tmpCls.TransCode
            tmpParas(18).Value = tmpCls.ProjectName
            tmpParas(19).Value = tmpCls.ProjectCode
            tmpParas(20).Value = tmpCls.ProjectCustomer
            tmpParas(21).Value = tmpCls.VoucherNumber
            tmpParas(22).Value = tmpCls.VoucherGroupId
            tmpParas(23).Value = tmpCls.VoucherGroup
            tmpParas(24).Value = tmpCls.WorkPeriod

            '执行更新查询(插入、修改、删除)
            tmpReturn = tmpDbAccess.InsertData(tmpSql, tmpParas, True)

            '关闭数据库
            tmpDbAccess.DbClose()

            Return tmpReturn

        Catch ex As OleDb.OleDbException

            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "ClsTVoucherDetailDbAccess - InsertEntry"

            Return 0
        End Try
    End Function

    ''' <summary>
    ''' 返回指定会计期间的全部分录
    ''' </summary>
    ''' <param name="wp"></param>
    ''' <returns></returns>
    Public Function GetAllEntriesInWorkPeriod(ByVal wp As String) As List(Of ClsTVoucherDetail)

        Dim tmpList As List(Of ClsTVoucherDetail) = New List(Of ClsTVoucherDetail)()

        Try
            Dim tmpSql As String
            Dim tmpParas(0) As OleDb.OleDbParameter

            Dim tmpDbAccess = GetConnection()

            '打开数据库
            tmpDbAccess.DbOpen()
            '构造查询语句
            tmpSql = "select * from [" & Me.AppTableName & "] where [WorkPeriod]=@WorkPeriod order by [VoucherGroupId], [EntryNumber] asc "
            tmpParas(0) = New OleDb.OleDbParameter("@WorkPeriod", OleDb.OleDbType.VarWChar)
            tmpParas(0).Value = wp

            '查询并将返回数据解析为List对象
            tmpList = CType(ConvertRecordToClass(tmpDbAccess.ExecuteSqlReturnDataSet(tmpSql, tmpParas)), List(Of ClsTVoucherDetail))

            '关闭数据库
            tmpDbAccess.DbClose()

        Catch ex As OleDb.OleDbException

            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "ClsTVoucherDetailDbAccess - GetAllEntriesInWorkPeriod"

        End Try

        Return tmpList

    End Function


    ''' <summary>
    ''' 返回指定凭证的全部分录
    ''' </summary>
    ''' <param name="autoId">指定凭证的自动编号</param>
    ''' <returns>分录集合,没有则返回空集合</returns>
    Public Function GetAllEntries(ByVal autoId As Integer) As List(Of ClsTVoucherDetail)

        Dim tmpList As List(Of ClsTVoucherDetail) = New List(Of ClsTVoucherDetail)()

        Try
            Dim tmpSql As String
            Dim tmpParas(0) As OleDb.OleDbParameter

            Dim tmpDbAccess = GetConnection()

            '打开数据库
            tmpDbAccess.DbOpen()
            '构造查询语句
            tmpSql = "select * from [" & Me.AppTableName & "] where [VoucherId]=@VoucherId order by [EntryNumber] asc "
            tmpParas(0) = New OleDb.OleDbParameter("@VoucherId", OleDb.OleDbType.Integer)
            tmpParas(0).Value = autoId

            '查询并将返回数据解析为List对象
            tmpList = CType(ConvertRecordToClass(tmpDbAccess.ExecuteSqlReturnDataSet(tmpSql, tmpParas)), List(Of ClsTVoucherDetail))

            '关闭数据库
            tmpDbAccess.DbClose()

        Catch ex As OleDb.OleDbException

            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "ClsTVoucherDetailDbAccess - GetAllVoucherInWorkPeriod"

        End Try

        Return tmpList

    End Function

    Public Function DeleteEntries(ByVal tmpId As Integer) As Integer

        Try
            Dim tmpSql As String
            Dim tmpDbAccess = GetConnection()

            Dim tmpParas(0) As OleDb.OleDbParameter
            Dim tmpReturn As Integer

            '打开数据库
            tmpDbAccess.DbOpen()

            '构造查询语句
            tmpSql = "Delete * From [" & Me.AppTableName & "] "
            tmpSql &= " where [VoucherId]=@VoucherId "

            '构造参数集合
            tmpParas(0) = New OleDb.OleDbParameter("@VoucherId", OleDb.OleDbType.Integer)

            tmpParas(0).Value = tmpId

            '执行更新查询(插入、修改、删除)
            tmpReturn = tmpDbAccess.ExecuteSqlNoDataReturn(tmpSql, tmpParas)

            '关闭数据库
            tmpDbAccess.DbClose()

            Return tmpReturn

        Catch ex As OleDb.OleDbException

            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "ClsTVoucherDbAccess -DeleteEntries"

            Return 0
        End Try

    End Function

    ''' <summary>
    ''' 判断指定科目是否已被凭证使用。
    ''' </summary>
    ''' <param name="a">指定科目编码</param>
    ''' <returns>是/否</returns>
    Public Function AcctExistInVoucher(ByVal a As String) As Boolean
        Try
            Dim tmpDb As ClsDbAccessMdb = GetConnection()
            Dim retInt As Integer
            Dim retBool As Boolean
            Dim tmpParas(0) As OleDb.OleDbParameter
            Dim tmpSql As String = "Select Count(AccountCode) as AcctCount from  [" & Me.AppTableName & "] "
            tmpSql &= " where [AccountCode] = @AccountCode "

            tmpParas(0) = New OleDb.OleDbParameter("@AccountCode", OleDb.OleDbType.VarWChar)
            tmpParas(0).Value = a

            '打开数据库
            tmpDb.DbOpen()

            '执行插入
            Dim tmpReader As OleDb.OleDbDataReader = tmpDb.ExecuteSqlReturnDataSet(tmpSql, tmpParas)
            If tmpReader.Read Then
                '有，则判断数量
                retInt = tmpReader.GetInt32(0)
            End If

            If retInt > 0 Then
                retBool = True
            Else
                retBool = False
            End If
            '关闭数据库
            tmpDb.DbClose()

            '返回结果
            Return retBool

        Catch ex As OleDb.OleDbException

            mErrorCreater = "ClsTAccountDetailDbAccess - AcctExistInVoucher"
            mErrorMessage = ex.Message
            mErrorNumber = ex.ErrorCode

            Return False
        End Try
    End Function


    Public Overrides Function ConvertRecordToClass(reader As OleDb.OleDbDataReader) As Object
        If reader Is Nothing Then
            Return Nothing
        End If

        Dim tmpList As List(Of ClsTVoucherDetail) = New List(Of ClsTVoucherDetail)

        Try

            Dim tmpCls As ClsTVoucherDetail

            While reader.Read()

                tmpCls = New ClsTVoucherDetail()

                With tmpCls

                    .DetailId = reader.GetInt32(0)
                    .VoucherId = reader.GetInt32(1)
                    .EntryNumber = reader.GetInt32(2)
                    .Excerpta = reader.GetString(3)
                    .AccountCode = reader.GetString(4)
                    .AccountCodeName = reader.GetString(5)
                    .Debit = reader.GetDecimal(6)
                    .Credit = reader.GetDecimal(7)
                    .Quantity = reader.GetFloat(8) '此字段数据库内为single
                    .Price = reader.GetDecimal(9) '此字段为 currency
                    .Unit = ConvertDbNullString(reader(10))
                    .SettleMode = ConvertDbNullString(reader(11))
                    .SettleCode = ConvertDbNullString(reader(12))
                    .SettleDate = ConvertDbNullDateTime(reader(13))
                    .Payer = ConvertDbNullString(reader(14))
                    .CurrencyCode = ConvertDbNullString(reader(15))
                    .ExchangeRate = reader.GetFloat(16) '此字段为single
                    .CurrencyNumber = reader.GetDecimal(17) '此字段为currency
                    .TransCode = ConvertDbNullString(reader(18))
                    .ProjectName = ConvertDbNullString(reader(19))
                    .ProjectCode = ConvertDbNullString(reader(20))
                    .ProjectCustomer = ConvertDbNullString(reader(21))
                    .VoucherNumber = reader.GetInt32(22)
                    .VoucherGroupId = reader.GetInt32(23)
                    .VoucherGroup = reader.GetString(24)
                    .WorkPeriod = ConvertDbNullSingle(reader(25))
                End With

                tmpList.Add(tmpCls)

            End While

            Return tmpList

        Catch ex As OleDb.OleDbException

            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "ClsTVoucherDetailDbAccess - ConvertRecordToClass"

            Return Nothing
        End Try

    End Function
End Class
